package com.asdan.seededu.front.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.asdan.seededu.Calenda;
import com.asdan.seededu.entity.security.Team;
import com.asdan.seededu.entity.security.User;
import com.asdan.seededu.entity.security.Userdata;
import com.asdan.seededu.front.service.IsweepStep2Service;
import com.asdan.seededu.util.DatabaseAccess;
import com.demo2do.core.utils.JsonUtils;

public class IsweepStep2ServiceImpl implements IsweepStep2Service {
	
	public Userdata getUserdata(int uid, int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Userdata  ud  = null ;
 		String sql="SELECT uid , ifleader , spid , teamid ,data FROM v_user_group_subproject Where spid =? AND uid = ? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, spid) ;
			pstmt.setInt(2, uid) ;
		    rs=pstmt.executeQuery();
			while(rs.next()){
				ud = new Userdata();
				Team t = new Team() ;
				ud.setUid(rs.getInt("uid")) ;
				ud.setIsleader(rs.getInt("ifleader")) ;
				ud.setSpid(rs.getInt("spid")) ;
				ud.setUser(JsonUtils.parse(rs.getString("data"), User.class)) ;
				t.setTeamid(rs.getInt("teamid")) ;
				ud.setTeam(t) ;
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return ud;
	}

	public boolean createTeam(int uid, int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		boolean temp=true;
 		String sql="insert into team(creattime , teamname , ifdelete ) values(?,?,?) ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setString(1, Calenda.getyyyymmddHHmmss()) ;
			pstmt.setString(2, "") ;
			pstmt.setInt(3, 0) ;
		    pstmt.execute();
		    int teamid = 0  ;
		    sql = " select  max(teamid) teamid from team " ;
			pstmt = cn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				teamid = rs.getInt("teamid")  ;
			}
			sql="update userdata  set  teamid = ? , ifleader = ? where uid = ? and spid =?  " ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setInt(1, teamid) ;
			pstmt.setInt(2,1) ;
			pstmt.setInt(3, uid) ;
			pstmt.setInt(4,spid) ;
			pstmt.executeUpdate() ;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp=false;
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
				
					pstmt = null ;
					cn = null ;
					temp=false;
			}
		}
		return temp;
	}

	public boolean addUserToTeam(int uid, int spid, int teamid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		boolean temp=true;
 		String sql="select teamid from userdata where uid =? and spid =? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, uid) ;
			pstmt.setInt(2, spid) ;
			rs = pstmt.executeQuery();
			int isteamid = 0 ;
			while(rs.next()){
				isteamid = rs.getInt("teamid")  ;
			}
			if(isteamid!=0){
				return false ;
			}
			sql="update userdata  set  teamid = ? , ifleader = ? where uid = ? and spid =?  " ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setInt(1, teamid) ;
			pstmt.setInt(2, 0) ;
			pstmt.setInt(3, uid) ;
			pstmt.setInt(4,spid) ;
			pstmt.executeUpdate() ;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp=false;
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
				
					pstmt = null ;
					cn = null ;
					temp=false;
			}
		}
		return temp;
	}

	public boolean removeUserFromTeam(int uid, int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		boolean temp=true;
 		String sql="update userdata  set  teamid = ? , ifleader = ? where uid = ? and spid =?  " ;
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, 0) ;
			pstmt.setInt(2, 2) ;
			pstmt.setInt(3, uid) ;
			pstmt.setInt(4,spid) ;
			pstmt.executeUpdate() ;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp=false;
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
				
					pstmt = null ;
					cn = null ;
					temp=false;
			}
		}
		return temp;
	}

	public boolean dismissTeam(int spid, int teamid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		boolean temp=true;
 		String sql="update team  set  ifdelete = ?  where teamid =?  " ;
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, 1) ;
			pstmt.setInt(2, teamid) ;
			pstmt.executeUpdate() ;
			sql="update userdata  set  teamid = ? , ifleader = ? where teamid = ? and spid =?  " ;
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, 0) ;
			pstmt.setInt(2, 2) ;
			pstmt.setInt(3,teamid) ;
			pstmt.setInt(4,spid) ;
			pstmt.executeUpdate() ;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp=false;
			
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
				
					pstmt = null ;
					cn = null ;
					temp=false;
			}
		}
			return temp;
	}

	public Team getTeam(int uid, int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Team t = new Team();
		t.setUlist(new ArrayList<Userdata>()) ;
 		String sql="SELECT teamname,creattime,ifdelete,teamid FROM v_user_group_subproject Where spid =? AND uid = ? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, spid) ;
			pstmt.setInt(2, uid) ;
		    rs=pstmt.executeQuery();
			while(rs.next()){
				t.setTeamname(rs.getString("teamname")) ;
				t.setCreattime(rs.getString("creattime")) ;
				t.setTeamid(rs.getInt("teamid")) ;
			}
				sql = "SELECT spid ,data ,ifleader from userdata where spid =? and teamid =? " ;
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, spid) ;
				pstmt.setInt(2, t.getTeamid()) ;
			    rs=pstmt.executeQuery();
				while(rs.next()){
					Userdata  ud =new Userdata();
					ud.setSpid(rs.getInt("spid")) ;
					ud.setUser(JsonUtils.parse(rs.getString("data"), User.class)) ;
					ud.setIsleader(rs.getInt("ifleader")) ;
					t.getUlist().add(ud) ;
				}
				t.setNum(t.getUlist().size()) ;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		System.out.println("t.getUlist().size() "+t.getUlist().size());
		return t;
	}
	public List<Team> getTeams(String schoolname, int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Team> tlist = new ArrayList<Team>();
 		String sql="SELECT count(teamid) as num,teamname,creattime,ifdelete,teamid FROM v_user_group_subproject Where spid =? AND data like '%"+schoolname+"%' AND teamid!=0 GROUP BY v_user_group_subproject.teamid";
 		System.out.println(sql);
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, spid) ;
		    rs=pstmt.executeQuery();
			while(rs.next()){
				Team  t =new Team();
				t.setNum(rs.getInt("num")) ;
				t.setTeamname(rs.getString("teamname")) ;
				t.setCreattime(rs.getString("creattime")) ;
				t.setTeamid(rs.getInt("teamid")) ;
				tlist.add(t) ;
			}
			sql = "SELECT spid ,data , ifleader from userdata where spid =? and teamid =? " ;
			for(int i=0;i<tlist.size();i++){
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, spid) ;
				pstmt.setInt(2, tlist.get(i).getTeamid()) ;
				tlist.get(i).setUlist(new ArrayList<Userdata>()) ;
			    rs=pstmt.executeQuery();
				while(rs.next()){
					Userdata  ud =new Userdata();
					ud.setSpid(rs.getInt("spid")) ;
					ud.setUser(JsonUtils.parse(rs.getString("data"), User.class)) ;
					ud.setIsleader(rs.getInt("ifleader")) ;
					tlist.get(i).getUlist().add(ud) ;
				}
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return tlist;
	}

	public List<Userdata> getUserdata(String schoolname, int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Userdata> ulist = new ArrayList<Userdata>();
 		String sql="SELECT uid , ifleader , spid , teamid ,data FROM v_user_group_subproject Where spid =? AND data like '%"+schoolname+"%' ";
 		System.out.println(sql);
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, spid) ;
		    rs=pstmt.executeQuery();
			while(rs.next()){
				Userdata  ud =new Userdata();
				Team t = new Team() ;
				ud.setUid(rs.getInt("uid")) ;
				ud.setIsleader(rs.getInt("ifleader")) ;
				ud.setSpid(rs.getInt("spid")) ;
				ud.setUser(JsonUtils.parse(rs.getString("data"), User.class)) ;
				t.setTeamid(rs.getInt("teamid")) ;
				ud.setTeam(t) ;
				ulist.add(ud) ;
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return ulist;
	}

	

}
